﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Xml;

namespace StemServiceWebRole
{
    /// <summary>
    /// Interacts with SQL Server based database to perform create, retrieve, update and delete operations.
    /// </summary>
    public class DatabaseHandler
    {
        /// <summary>
        /// Initializes a new instance of DatabaseHandler.
        /// </summary>
        public DatabaseHandler()
        {
#if(DEBUG)
            // use local database in debug builds
            connectionString = @"Data Source=.\SQLExpress;Integrated Security=SSPI;Initial Catalog=STEM123";
#else
            // use sql azure based database in release builds
            connectionString = @"Data Source=sqmk4l5ipl.database.windows.net;Initial Catalog=STEM123;User ID=lavinder;Password=stem_123;Trusted_Connection=False;Encrypt=True";
#endif
        }

        /// <summary>
        /// Publishes metadata related to a publisher and term.
        /// </summary>
        /// <param name="publisher">Name of publisher.</param>
        /// <param name="term">Term of presentation.</param>
        /// <returns>Id of presentation, which is greater than zero else exception.</returns>
        public uint PublishMetadata(string publisher, string term)
        {
            uint presentationId = uint.MinValue;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("PublishMetadata", connection))
                {
                    // prepare command
                    command.CommandType = System.Data.CommandType.StoredProcedure;

                    // prepare parameters
                    SqlParameter publisherParam = new SqlParameter("@Publisher", System.Data.SqlDbType.NVarChar);
                    publisherParam.Value = publisher;

                    SqlParameter termParam = new SqlParameter("@Term", System.Data.SqlDbType.NVarChar);
                    termParam.Value = term;

                    // add parameters
                    command.Parameters.Add(publisherParam);
                    command.Parameters.Add(termParam);

                    // execute command
                    presentationId = uint.Parse(command.ExecuteScalar().ToString());
                }
            }

            return presentationId;
        }

        /// <summary>
        /// Retrieves current version of a presentation.
        /// </summary>
        /// <param name="presentationId">Id of presentation.</param>
        /// <returns>Current version.</returns>
        public uint GetVersion(uint presentationId)
        {
            uint version = uint.MinValue;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("GetPresentationVersion", connection))
                {
                    // prepare command
                    command.CommandType = System.Data.CommandType.StoredProcedure;

                    // prepare parameters
                    SqlParameter presentationIdParam = new SqlParameter("@PresentationId", System.Data.SqlDbType.NVarChar);
                    presentationIdParam.Value = presentationId;

                    // add parameters
                    command.Parameters.Add(presentationIdParam);

                    // execute command
                    version = uint.Parse(command.ExecuteScalar().ToString());
                }
            }

            return version;
        }

        /// <summary>
        /// Completes publishing of content after publishing metadata and uploading of content.
        /// </summary>
        /// <param name="presentationId">Id of presentation.</param>
        /// <param name="slides">Slides of presentation.</param>
        public uint PublishContent(uint presentationId, Slide[] slides)
        {
            uint version = uint.MinValue;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("PublishContent", connection))
                {
                    // prepare command
                    command.CommandType = System.Data.CommandType.StoredProcedure;

                    // prepare parameters
                    SqlParameter presentationIdParam = new SqlParameter("@PresentationId", System.Data.SqlDbType.BigInt);
                    presentationIdParam.Value = presentationId;

                    SqlParameter contentParam = new SqlParameter("@Content", System.Data.SqlDbType.Xml);
                    contentParam.Value = GetContentXml(slides);

                    // add parameters
                    command.Parameters.Add(presentationIdParam);
                    command.Parameters.Add(contentParam);

                    // execute command
                    version = uint.Parse(command.ExecuteScalar().ToString());
                }
            }

            return version;
        }

        /// <summary>
        /// Searches for presentations related to a term (and publisher).
        /// </summary>
        /// <param name="publisher">Name of publisher (optional).</param>
        /// <param name="term">Term to be searched.</param>
        /// <returns>Results of search.</returns>
        public SearchResults Search(string publisher, string term)
        {
            List<Presentation> presentations = new List<Presentation>();
            List<Slide> slides = new List<Slide>();
            SearchResults results = new SearchResults();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("Search", connection))
                {
                    // prepare command
                    command.CommandType = System.Data.CommandType.StoredProcedure;

                    // prepare parameters
                    SqlParameter publisherParam = new SqlParameter("@Publisher", System.Data.SqlDbType.NVarChar);
                    if (string.IsNullOrEmpty(publisher))
                    {
                        publisherParam.Value = DBNull.Value;
                    }
                    else
                    {
                        publisherParam.Value = publisher;
                    }

                    SqlParameter termParam = new SqlParameter("@Term", System.Data.SqlDbType.NVarChar);
                    termParam.Value = term;

                    // add parameters
                    command.Parameters.Add(publisherParam);
                    command.Parameters.Add(termParam);

                    // execute command to retrieve records
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Presentation presentation = new Presentation();

                            presentation.Id = uint.Parse(reader["Id"].ToString());
                            presentation.PublishedOn = DateTime.Parse(reader["PublishedOn"].ToString());
                            presentation.TimesViewed = uint.Parse(reader["TimesViewed"].ToString());

                            // read xml blob containing information about slides of presentation
                            // and initialize Slide objects
                            XmlDocument document = new XmlDocument();
                            document.LoadXml(reader["Content"].ToString());

                            XmlNodeList nodes = document.DocumentElement.ChildNodes;
                            presentation.Slides = new Slide[nodes.Count];

                            for (int i = 0; i < nodes.Count; i++)
                            {
                                Slide slide = new Slide();

                                slide.Question = nodes[i].Attributes[ContentXmlConstants.Question].Value;
                                slide.Answer = nodes[i].Attributes[ContentXmlConstants.Answer].Value;
                                slide.MediaType = (MediaTypes)Enum.Parse(typeof(MediaTypes), nodes[i].Attributes[ContentXmlConstants.MediaType].Value);

                                string source = nodes[i].Attributes[ContentXmlConstants.ImageSource].Value;
                                if (string.IsNullOrEmpty(source))
                                {
                                    slide.ImageSource = null;
                                }
                                else
                                {
                                    slide.ImageSource = new Uri(source);
                                }

                                if (slide.MediaType == MediaTypes.AudioOnly || slide.MediaType == MediaTypes.AudioWithImage || slide.MediaType == MediaTypes.Video)
                                {
                                    slide.MediaSource = new Uri(nodes[i].Attributes[ContentXmlConstants.MediaSource].Value);
                                    slide.MediaDuration = uint.Parse(nodes[i].Attributes[ContentXmlConstants.MediaDuration].Value);
                                }
                                else
                                {
                                    slide.MediaSource = null;
                                    slide.MediaDuration = uint.MinValue;
                                }

                                presentation.Slides[i] = slide;
                            }

                            presentations.Add(presentation);
                        }
                    }
                }
            }

            if (presentations.Count > 0)
            {
                results.Presentations = presentations.ToArray();
            }

            return results;
        }

        /// <summary>
        /// Resets state of database, by deleting all records.
        /// </summary>
        public void ResetState()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("ResetState", connection))
                {
                    // prepare command
                    command.CommandType = System.Data.CommandType.StoredProcedure;

                    // execute command
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Converts slides into xml blob representing those slides.
        /// </summary>
        /// <param name="slides">Slides of a presentation.</param>
        /// <returns>Xml representation of slides.</returns>
        private string GetContentXml(Slide[] slides)
        {
            StringBuilder xmlBuilder = new StringBuilder();

            xmlBuilder.Append("<" + ContentXmlConstants.Presentation + ">");

            foreach (Slide slide in slides)
            {
                xmlBuilder.Append("<" + ContentXmlConstants.Slide + " ");
                xmlBuilder.Append(ContentXmlConstants.Question + "='" + slide.Question + "' ");
                xmlBuilder.Append(ContentXmlConstants.Answer + "='" + slide.Answer + "' ");
                xmlBuilder.Append(ContentXmlConstants.MediaType + "='" + (uint)slide.MediaType + "' ");

                if (slide.ImageSource != null)
                {
                    xmlBuilder.Append(ContentXmlConstants.ImageSource + "='" + slide.ImageSource + "' ");
                }
                else
                {
                    xmlBuilder.Append(ContentXmlConstants.ImageSource + "='' ");
                }

                if (slide.MediaType == MediaTypes.AudioOnly || slide.MediaType == MediaTypes.AudioWithImage || slide.MediaType == MediaTypes.Video)
                {
                    xmlBuilder.Append(ContentXmlConstants.MediaSource + "='" + slide.MediaSource + "' " + ContentXmlConstants.MediaDuration + "='" + slide.MediaDuration + "'");
                }
                else
                {
                    xmlBuilder.Append(ContentXmlConstants.MediaSource + "='' " + ContentXmlConstants.MediaDuration + "='0'");
                }

                xmlBuilder.Append("/>");
            }

            xmlBuilder.Append("</" + ContentXmlConstants.Presentation + ">");

            return xmlBuilder.ToString();
        }

        /// <summary>
        /// Constants used in xml blobs used to representation slides
        /// of presentations.
        /// </summary>
        private static class ContentXmlConstants
        {
            public const string Presentation = "presentation";
            public const string Slide = "slide";
            public const string Question = "question";
            public const string Answer = "answer";
            public const string MediaType = "mediatype";
            public const string ImageSource = "imagesource";
            public const string MediaSource = "mediasource";
            public const string MediaDuration = "mediaduration";
        }

        /// <summary>
        /// Database connection string.
        /// </summary>
        private string connectionString;
    }
}